package com.codinghero.zqlb.spider.dao;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import com.codinghero.util.ArrayUtils;
import com.codinghero.util.DateUtils;
import com.codinghero.util.StringUtils;
import com.codinghero.zqlb.match.SoccerMatch;

public class MatchDao extends BaseDao {
	
	// ---select---
	public List<SoccerMatch> getAllMatches() throws SQLException {
		return dbOper.selectList(
						"SELECT * FROM zqlb_soccer_match ORDER BY startDate asc",
						SoccerMatch.class);
	}
	
	public List<SoccerMatch> getAllValidMatches() throws SQLException {
		return dbOper.selectList(
						"SELECT * FROM zqlb_soccer_match WHERE status=1 ORDER BY startDate asc",
						SoccerMatch.class);
	}

	public List<SoccerMatch> getMatchesByTypeIds(Integer[] typeIds) throws SQLException {
		String typeIdStr = StringUtils.join(ArrayUtils.toList(typeIds), ",");
		return dbOper.selectList(
				"SELECT * FROM zqlb_soccer_match WHERE status=1 AND typeId in (" + typeIdStr + ")  ORDER BY startDate asc", SoccerMatch.class);
	}

	public List<SoccerMatch> getMatchesByDate(String startDate, String endDate) throws SQLException {
		String sql = "SELECT * FROM zqlb_soccer_match WHERE status=1 "
				+ "AND startDate>='" + startDate + "' AND startDate<='" + endDate 
				+ "' ORDER BY startDate asc";
		return dbOper.selectList(sql, SoccerMatch.class);
	}
	
	// ---insert---
	public int insertTempMatch(SoccerMatch match) throws SQLException {
		String sql = "INSERT INTO zqlb_soccer_match_temp "
				+ " (`typeId`, typeName, startDate, endDate, "
				+ " team1Id, team2Id, team1Name, team2Name, " +
				" typeFocused, team1Focused, team2Focused, " +
				" channelIds, channelNames, insertTime) " + "VALUES ("
				+ match.getTypeId() + ", '" + match.getTypeName() + "', '"
				+ match.getStartDateStr() + "', NULL, "
				+ match.getTeam1Id() + ", " + match.getTeam2Id() + ", '"
				+ match.getTeam1Name() + "', '" + match.getTeam2Name() + "', " 
				+ match.getTypeFocused() + ", " + match.getTeam1Focused() + ", " + match.getTeam2Focused() + ", '"
				+ match.getChannelIds() + "', '" + match.getChannelNames() + "', NOW())";
		return dbOper.executeUpdate(sql);
	}
	
	public int insertTempToMatch() throws SQLException {
		return dbOper.executeUpdate("INSERT INTO zqlb_soccer_match" +
				"(typeId, typeName, startDate, endDate, team1Id, team2Id, " +
				"team1Name, team2Name, typeFocused, team1Focused, team2Focused, " +
				"channelIds, channelNames, " +
				"status, insertTime) SELECT " +
				"typeId, typeName, startDate, endDate, team1Id, team2Id, " +
				"team1Name, team2Name, typeFocused, team1Focused, team2Focused, " +
				"channelIds, channelNames, " +
				"status, NOW() " +
				"FROM zqlb_soccer_match_temp");
	}
	
	public int insertMatchToHistory(Date time) throws SQLException {
		String sql = "insert into zqlb_soccer_match_history select * from zqlb_soccer_match "
				+ " where startDate < subtime('" + DateUtils.getTimeStr(time) + "', '03:00:00')";
		return dbOper.executeUpdate(sql);
	}
	
	
	// ---update---
	public int enableMatches() throws SQLException {
		return dbOper.executeUpdate("UPDATE zqlb_soccer_match m SET m.status=1 WHERE m.status=0");
	}
	
	public int updateMatchStatusByIds(String[] ids, int status) throws SQLException {
		String idsJoinComma = StringUtils.join(ArrayUtils.toList(ids), ",");
		return dbOper.executeUpdate("UPDATE zqlb_soccer_match m SET m.status="
				+ status + " WHERE m.id in (" + idsJoinComma + ")");
	}
	
	/**
	 * update date & channel 
	 * @return
	 * @throws SQLException
	 */
	public int[] updateMatchTime() throws SQLException {
		// 调整typeId,teamId相同，时间差在72小时之内的比赛时间
		dbOper.addBatch("UPDATE zqlb_soccer_match m, zqlb_soccer_match_temp t " +
				" SET m.startDate=t.startDate " +
				" WHERE ((m.typeId=t.typeId AND m.team1Id=t.team1Id AND m.team2Id=t.team2Id) " +
				" OR (m.typeName=t.typeName AND m.team1Name=t.team1Name AND m.team2Name=t.team2Name)) " +
				" AND m.startDate!=t.startDate " +
				" AND m.startDate > subtime(t.startDate,  '72:00:00') " +
				" AND m.startDate < addtime(t.startDate, '72:00:00') ");
		// 若typeId,teamId,startDate相同，更新直播信息
		dbOper.addBatch("UPDATE zqlb_soccer_match m, zqlb_soccer_match_temp t " +
				" SET m.channelIds=t.channelIds, m.channelNames=t.channelNames " +
				" WHERE ((m.typeId=t.typeId AND m.team1Id=t.team1Id AND m.team2Id=t.team2Id) " +
				" OR (m.typeName=t.typeName AND m.team1Name=t.team1Name AND m.team2Name=t.team2Name)) " +
				" AND m.startDate=t.startDate " +
				" AND ( m.channelIds!=t.channelIds OR m.channelNames!=t.channelNames )");
		return dbOper.executeBatch();
	}
	

	// ---delete---
	public int[] deleteDupMatches() throws SQLException {
		dbOper.addBatch("DELETE t FROM zqlb_soccer_match_temp t, zqlb_soccer_match m "
				+ " WHERE t.startDate = m.startDate AND t.typeId=m.typeId "
				+ " AND t.team1Id = m.team1Id AND t.team2Id=m.team2Id");
		dbOper.addBatch("DELETE t FROM zqlb_soccer_match_temp t, zqlb_soccer_match m "
				+ " WHERE t.startDate = m.startDate AND t.typeName=m.typeName "
				+ " AND t.team1Name = m.team1Name AND t.team2Name=m.team2Name");
		return dbOper.executeBatch();
	}
	
	public int deleteHistoryMatch(Date time) throws SQLException {
		return dbOper.executeUpdate("delete from zqlb_soccer_match " +
				" where startDate < subtime('" + DateUtils.getTimeStr(time) + "', '03:00:00')");
	}
	
	
	// ---etc---
	public int truncateTempMatch() throws SQLException {
		return dbOper.executeUpdate("TRUNCATE TABLE zqlb_soccer_match_temp");
	}
}
